<?php
/**
 * Offices Activity Followup
 *
 */

use Doctrine\DBAL;
use Fox\Authorization;
use Fox\Dao\SqlFragment;
use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Connection;
use Fox\Grid\FieldCat\Field;
use Fox\Common\Customer;
use Fox\SalesDistribution\Sales\Partner\Role;
use Fox\SalesDistribution\Sales\Inquiry\Partner;
use Fox\Html\Screen;
use Fox\SalesDistribution\Sales\Inquiry;
use Fox\SalesDistribution\Sales\Inquiry\Status;
$xview = Xview::instance();

$screen = new Screen("eSelect","execute");
$screen->mainTitle();

$block = new Fox\Html\Block("gCriteria",Msg(Fox::MSGCL,29));
$block->add(new Fox\Html\Input("period",true,time()));
// Agency Selection Part
$finder = new Customer();
$agFragments[] = new Fox\Dao\SqlFragment("type = ?",array(Fox\Common\Customer::TYPE_OFFICE));
if(!Fox\Authorization\Manager::check(User::AUTH_VIEW_ALL))
{
	$agFragments[] = new Fox\Dao\SqlFragment("customerid IN (?)",
											array(User::current()->agency()),
											array(Connection::PARAM_STR_ARRAY));
}
$agencies = $finder->find(Fox\Dao\SqlFragment::combine($agFragments),array("name1" => false));
$block->add(new Fox\Html\DropList\CheckBox("op_entity",null,null,null,null,null,Fox\Html\DropList\CheckBox::RMODE_VERTICAL))
				->nbOfItemByBlock(3)
				->options($agencies,Fox\Generator::useAttribute('customerid'),
								Fox\Generator::useAttribute('name1'));

$screen->add($block);
if($screen->submitted())
{
	$inputs = $screen->get();
	if(!$screen->errors())
	{
		$selectedDate = new DateTime($inputs['period']);
		$endDate = new DateTime($selectedDate->format('Y-m')."-01");
		$endDate->add(new DateInterval("P1M"));
		$startDate = new DateTime($selectedDate->format('Y')."-01-01");
		// Grid
		$grid = new Fox\Grid("ReportingAgence");
		$grid->setXlsDownload();
		$grid->fieldcat(array("status"));
		$grid->fieldcat()->field("status")->setClass("separator-right pad",true)
												->setNoWrap(true);
		$agPostFragments[] = $block->item('op_entity')->fragments('customerid');
		$agPostFragments[] = new SqlFragment("type = ?",array(Fox\Common\Customer::TYPE_OFFICE));
		$agencies = $finder->find(SqlFragment::combine($agPostFragments),array("name1" => false));
		// Add a Socotec Total Agency
		$finder = new Customer();
		$res = $finder->find(new SqlFragment("type = ?",array(Customer::TYPE_US)));
		if(isset($res[0]))
		{
			$idAgTotal =  $res[0]->getId();
			$agencies[] = $res[0];
		}

		foreach($agencies as $agency)
		{
			$id = $agency->getId();
			$fieldName1 = "amount_$id";
			$field = new Field("amount",$fieldName1);
			$field->setClass("pad",true)->align("right");
			$grid->fieldcat()->add($field);
			$fieldName2 = "number_$id";
			$field = new Field("number",$fieldName2);
			$field->setClass("separator-dashed-left separator-right",true)->align("right");
			$grid->fieldcat()->add($field);
			$groupingField = new Field(array("agency","agency_$id"));
			$groupingField->setClass("gd-col-group separator-left")->label($agency->name1);
			$grid->fieldcat()->buildSubTitleByGrouping(array($fieldName1,$fieldName2), $groupingField);

			$filterAgencies[] = $id;
		}

		$grid->fieldcat()->add(new Field("cssclass"));
		$grid->fieldcat()->field("cssclass")->noOutput();
		$grid->fieldcat()->setClassField("cssclass");
		$grid->layout()->scrollHorizontal();
		
		$finder = new Status();
		$status = $finder->find(new Fox\Dao\SqlFragment("statusid != ?",array(1)));
		foreach($status as $item)
		{
			$data[$item->getId()] = $grid->GetEmptyLine(array("status" => $item->label()));
			$statusId[$item->getId()] = $item->getId();
		}
		$data["total"] 			= $grid->GetEmptyLine(array("status" => "Total","cssclass" => "xv-grid-total"));
		$data["txsuccess"] 		= $grid->GetEmptyLine(array("status" => "Taux de r&eacute;uissite"));
		$data["txfailure"] 		= $grid->GetEmptyLine(array("status" => "Taux de perte"));
		$data["rate_wip"] 		= $grid->GetEmptyLine(array("status" => "En cours"));
		$data["rate_cancel"] 	= $grid->GetEmptyLine(array("status" => "Annul&eacute;s"));

		$data["month"] = $grid->GetEmptyLine(array("status" => "Offre du Mois"));
		$data["month_avg"] = $grid->GetEmptyLine(array("status" => "Moyenne Mensuelle"));
		$data["month_rate"] = $grid->GetEmptyLine(array("status" => "Offres du mois/moyenne"));
	//	$data["relance"] = $grid->GetEmptyLine(array("status" => "Relances faites sur les offres en cours"));

		// Load Data
		$query = \Fox\Dao::infos(Inquiry::className())->handle()->createQueryBuilder();
		$queryTotal = clone $query;
		$query->select('t.status','pa.customerid','SUM(total1) AS total1','SUM(total2) AS final','COUNT(*) as nb')
			   ->from('inquiries','t')
			   ->innerJoin('t', 'inquiry_partners', 'pa','t.inquiryid = pa.inquiryid')
			   ->groupBy('t.status','pa.customerid');
		$fragments[] = new Fox\Dao\SqlFragment("t.loekz = ? ",array(0));
		$fragments[] = new Fox\Dao\SqlFragment("start_date >= ? AND start_date < ? ",
											array($startDate,$endDate),
											array(Type::DATE,Type::DATE));
		$fragments[] = new SqlFragment("pa.customerid IN (?) AND pa.role = ? ",
										array($filterAgencies,Role::RESPONSIBLE_AGENCY),
										array(Connection::PARAM_INT_ARRAY,Type::INTEGER));
		if(!Fox\Authorization\Manager::check(User::AUTH_VIEW_ALL))
			$fragments[] = User::current()->inquiryFragments();
		$fragment = Fox\Dao\SqlFragment::combine($fragments);
		$queryMonth = clone $query;
		$result = $fragment->pushIn($query)->execute()->fetchAll(PDO::FETCH_ASSOC);
		foreach($result as $item)
		{
			$id 	= $item['customerid'];
			$status = $item['status'];
			$amount =  $item['total1'];
			//$data[$status]["amount_$id"] += ($item['final'] > 0) ? $item['final'] : !empty($item['revised']) ? $item['revised'] : $item['initial'];
			$data[$status]["amount_$id"] += $amount;
			$data["total"]["amount_$id"] += $amount;
			$data[$status]["number_$id"] += $item['nb'];
			$data["total"]["number_$id"] += $item['nb'];
			// Prepare Last Column Total
			/*
			$data[$status]["amount_$idAgTotal"] += $amount;
			$data["total"]["amount_$idAgTotal"] += $amount;
			$data[$status]["number_$idAgTotal"] += $item['nb'];
			$data["total"]["number_$idAgTotal"] += $item['nb'];
			*/
		}						
		// Load Data For the current month only
		$monthStartDate =  new DateTime($selectedDate->format('Y-m')."-01");
		$fragments[] = new Fox\Dao\SqlFragment("t.loekz = ? AND pa.role = ?",array(0,Role::RESPONSIBLE_AGENCY));
		$fragments[] = new Fox\Dao\SqlFragment("start_date >= ? AND start_date < ? ",
											array($monthStartDate,$endDate),
											array(Type::DATE,Type::DATE));
		if(!Fox\Authorization\Manager::check(User::AUTH_VIEW_ALL))
			$fragments[] = User::current()->inquiryFragments();
		$fragment = Fox\Dao\SqlFragment::combine($fragments);
		$result = $fragment->pushIn($queryMonth)->execute()->fetchAll(PDO::FETCH_ASSOC);
		foreach($result as $item)
		{
			$id 	= $item['customerid'];
			//$amount = ($item['final'] > 0) ? $item['final'] : ($item['revised'] > 0) ? $item['revised'] : $item['initial'];
			//$amount = ($item['revised'] > 0) ? $item['revised'] : $item['initial'];
			$data["month"]["amount_$id"] += $item['total1'];
			$data["month"]["number_$id"] += $item['nb'];
		}
		$fragments = array();
		// Load Socotec Master Total Information
		$queryTotal->select('t.status','SUM(total1) AS total1','SUM(total2) AS final','COUNT(*) as nb')
			   ->from('inquiries','t')
			   ->groupBy('t.status');
		$fragments[] = new Fox\Dao\SqlFragment("t.loekz = ? ",array(0));
		$fragments[] = new Fox\Dao\SqlFragment("start_date >= ? AND start_date < ? ",
											array($startDate,$endDate),
											array(Type::DATE,Type::DATE));
		$fragment = Fox\Dao\SqlFragment::combine($fragments);
		$result = $fragment->pushIn($queryTotal)->execute()->fetchAll(PDO::FETCH_ASSOC);
		$data["total"]["amount_$idAgTotal"] = $data["total"]["number_$idAgTotal"] = 0;
		foreach($result as $item) {
			$status = $item['status'];
			$amount =  $item['total1'];
			// Prepare Last Column Total
			$data[$status]["amount_$idAgTotal"] = $amount;
			$data["total"]["amount_$idAgTotal"] += $amount;
			$data[$status]["number_$idAgTotal"] = $item['nb'];
			$data["total"]["number_$idAgTotal"] += $item['nb'];
		}					
		// Number of dunning on WIP inquiries
		$monthNb = $selectedDate->format("m");
		// Caculate Rates
		foreach($agencies as $agency)
		{
			$id = $agency->getId();

			if($data["total"]["amount_$id"] > 0 )
			{
				$rate = $data[Inquiry::STATUS_WIN]["amount_$id"] / $data["total"]["amount_$id"] * 100;
				$data["txsuccess"]["amount_$id"] = round($rate)."%";
				$rate = $data[Inquiry::STATUS_WIN]["number_$id"] / $data["total"]["number_$id"] * 100;
				$data["txsuccess"]["number_$id"] = round($rate)."%";
				$rate = $data[Inquiry::STATUS_LOST]["amount_$id"] / $data["total"]["amount_$id"] * 100;
				$data["txfailure"]["amount_$id"] = round($rate)."%";
				$rate = $data[Inquiry::STATUS_LOST]["number_$id"] / $data["total"]["number_$id"] * 100;
				$data["txfailure"]["number_$id"] = round($rate)."%";
				$rate = $data[Inquiry::STATUS_WIP]["amount_$id"] / $data["total"]["amount_$id"] * 100;
				$data["rate_wip"]["amount_$id"] = round($rate)."%";
				$rate = $data[Inquiry::STATUS_WIP]["number_$id"] / $data["total"]["number_$id"] * 100;
				$data["rate_wip"]["number_$id"] = round($rate)."%";
				$rate = $data[Inquiry::STATUS_CANCEL]["amount_$id"] / $data["total"]["amount_$id"] * 100;
				$data["rate_cancel"]["amount_$id"] = round($rate)."%";
				$rate = $data[Inquiry::STATUS_CANCEL]["number_$id"] / $data["total"]["number_$id"] * 100;
				$data["rate_cancel"]["number_$id"] = round($rate)."%";

				$data["month_avg"]["amount_$id"] = $data["total"]["amount_$id"] / $monthNb;
				if($data["month_avg"]["amount_$id"] > 0)
				{
					$data["month_rate"]["amount_$id"] = $data["month"]["amount_$id"] / $data["month_avg"]["amount_$id"];
				}

			}
			else
			{
				//$data["txsuccess"]["amount_$id"] = "0%";
			}

		}
		//
		if(Authorization\Manager::check(User::AUTH_VIEW_ALL))
		{

		}
		$grid->initialize($data);
		$output = $grid->Display();
	}
}
echo $screen->Display();
if(isset($output))
	echo $output;